﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IODataAccess;
using IOEntity;
using System.Data.SqlClient;
using System.Data;

namespace IODataAccess
{
    public class clsDATour
    {
        clsDAConnection con = new clsDAConnection();
        public clsTour GetById(int id)
        {
            try
            {
                clsTour obj = new clsTour();
                obj.TheLoai = new clsTheLoai();
                string str = "select * from tbltour where id=@id";
                SqlCommand com = new SqlCommand(str, con.getCon());
                com.Parameters.AddWithValue("@id", id);
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                if (dr.Read())
                {
                    obj.Id = id;
                    if (dr["tieude"] != DBNull.Value)
                    {
                        obj.TieuDe = dr["tieude"].ToString();
                    }
                    if (dr["ghichu"] != DBNull.Value)
                    {
                        obj.GhiChu = dr["ghichu"].ToString();
                    }
                    if (dr["hinhanh"] != DBNull.Value)
                    {
                        obj.HinhAnh = dr["hinhanh"].ToString();
                    }
                    if (dr["hot"] != DBNull.Value)
                    {
                        obj.Hot = Convert.ToBoolean(dr["hot"].ToString());
                    }
                    if (dr["theloaiid"] != DBNull.Value)
                    {
                        obj.TheLoai.Id = Convert.ToInt32(dr["theloaiid"].ToString());
                    }
                }
                return obj;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                con.closeCon();
            }
        }
        public List<clsTour> GetAll()
        {
            try
            {
                List<clsTour> list = new List<clsTour>();
                string str = "select * from tblTour order by id DESC";
                SqlCommand com = new SqlCommand(str, con.getCon());
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    clsTour obj = new clsTour();
                    obj.TheLoai = new clsTheLoai();
                    if (dr["tieude"] != DBNull.Value)
                    {
                        obj.TieuDe = dr["tieude"].ToString();
                    }
                    if (dr["ghichu"] != DBNull.Value)
                    {
                        obj.GhiChu = dr["ghichu"].ToString();
                    }
                    if (dr["hinhanh"] != DBNull.Value)
                    {
                        obj.HinhAnh = dr["hinhanh"].ToString();
                    }
                    if (dr["hot"] != DBNull.Value)
                    {
                        obj.Hot = Convert.ToBoolean(dr["hot"].ToString());
                    }
                    if (dr["theloaiid"] != DBNull.Value)
                    {
                        obj.TheLoai.Id = Convert.ToInt32(dr["theloaiid"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                return list;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                con.closeCon();
            }
        }
        public List<clsTour> GetByLoai(int loaiid)
        {
            try
            {
                List<clsTour> list = new List<clsTour>();
                string str = "select * from tblTour where theloaiid=@theloaiid order by id DESC";
                SqlCommand com = new SqlCommand(str, con.getCon());
                com.Parameters.AddWithValue("@theloaiid", loaiid);
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    clsTour obj = new clsTour();
                    obj.TheLoai = new clsTheLoai();
                    if (dr["tieude"] != DBNull.Value)
                    {
                        obj.TieuDe = dr["tieude"].ToString();
                    }
                    if (dr["ghichu"] != DBNull.Value)
                    {
                        obj.GhiChu = dr["ghichu"].ToString();
                    }
                    if (dr["hinhanh"] != DBNull.Value)
                    {
                        obj.HinhAnh = dr["hinhanh"].ToString();
                    }
                    if (dr["hot"] != DBNull.Value)
                    {
                        obj.Hot = Convert.ToBoolean(dr["hot"].ToString());
                    }
                    if (dr["theloaiid"] != DBNull.Value)
                    {
                        obj.TheLoai.Id = Convert.ToInt32(dr["theloaiid"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                return list;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                con.closeCon();
            }
        }
        public List<clsTour> GetByHostTour()
        {
            try
            {
                List<clsTour> list = new List<clsTour>();
                string str = "select * from tblTour where Hot=1 order by id DESC";
                SqlCommand com = new SqlCommand(str, con.getCon());
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    clsTour obj = new clsTour();
                    obj.TheLoai = new clsTheLoai();
                    if (dr["tieude"] != DBNull.Value)
                    {
                        obj.TieuDe = dr["tieude"].ToString();
                    }
                    if (dr["ghichu"] != DBNull.Value)
                    {
                        obj.GhiChu = dr["ghichu"].ToString();
                    }
                    if (dr["hinhanh"] != DBNull.Value)
                    {
                        obj.HinhAnh = dr["hinhanh"].ToString();
                    }
                    if (dr["hot"] != DBNull.Value)
                    {
                        obj.Hot = Convert.ToBoolean(dr["hot"].ToString());
                    }
                    if (dr["theloaiid"] != DBNull.Value)
                    {
                        obj.TheLoai.Id = Convert.ToInt32(dr["theloaiid"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                return list;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                con.closeCon();
            }
        }
        public List<clsTour> GetByTop(int top)
        {
            try
            {
                List<clsTour> list = new List<clsTour>();
                string str = "select * from tblTour order by id DESC";
                SqlCommand com = new SqlCommand(str, con.getCon());
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                int dem = 0;
                while (dr.Read() || dem > top)
                {
                    dem++;
                    clsTour obj = new clsTour();
                    obj.TheLoai = new clsTheLoai();
                    if (dr["tieude"] != DBNull.Value)
                    {
                        obj.TieuDe = dr["tieude"].ToString();
                    }
                    if (dr["ghichu"] != DBNull.Value)
                    {
                        obj.GhiChu = dr["ghichu"].ToString();
                    }
                    if (dr["hinhanh"] != DBNull.Value)
                    {
                        obj.HinhAnh = dr["hinhanh"].ToString();
                    }
                    if (dr["hot"] != DBNull.Value)
                    {
                        obj.Hot = Convert.ToBoolean(dr["hot"].ToString());
                    }
                    if (dr["theloaiid"] != DBNull.Value)
                    {
                        obj.TheLoai.Id = Convert.ToInt32(dr["theloaiid"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                return list;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                con.closeCon();
            }
        }

        ///////////////////////
        public bool InsertTour(clsTour to)
        {
            string sql = "Insert into tbltour(Tieude,GhiChu,TheLoaiID,Hot,STT,HinhAnh) values(@tieude,@ghichu,@tlID,@hot,@stt,@HinhAnh)";
            bool st = false;
            clsDAConnection conn = new clsDAConnection();
            conn.openCon();
            using (SqlCommand com = conn.getCon().CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = sql;

                // Create a SqlParameter object for the title parameter.
                SqlParameter p1 = com.CreateParameter();
                p1.ParameterName = "@tieude";
                p1.SqlDbType = SqlDbType.NVarChar;
                p1.Size = 50;
                p1.Value = to.TieuDe;
                com.Parameters.Add(p1);

                // Use a shorthand syntax to add the id parameter.
                com.Parameters.Add("@tlID", SqlDbType.Int).Value = to.MyTheLoai;
                com.Parameters.Add("@ghichu", SqlDbType.NVarChar, 1000).Value = to.GhiChu;
                com.Parameters.Add("@stt", SqlDbType.Int).Value = to.Stt;
                com.Parameters.Add("@hot", SqlDbType.Int).Value = to.Hot;
                com.Parameters.Add("@HinhAnh", SqlDbType.NVarChar,50).Value = to.HinhAnh;
                // Execute the command and process the result.
                if (com.ExecuteNonQuery() == 1)
                {
                    st = true;
                }
                conn.closeCon();
                return st;
            }
        }

        // Sua danh muc
        public bool EditTour(clsTour to)
        {
            string sql = "Update tbltour set Tieude=@tieude,GhiChu=@ghichu,TheLoaiID=@tlID,Hot=@hot,STT=@stt,HinhAnh=@HinhAnh Where ID=@ID";
            bool st = false;
            clsDAConnection conn = new clsDAConnection();
            conn.openCon();
            using (SqlCommand com = conn.getCon().CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = sql;

                // Create a SqlParameter object for the title parameter.
               
                SqlParameter p1 = com.CreateParameter();
                p1.ParameterName = "@tieude";
                p1.SqlDbType = SqlDbType.NVarChar;
                p1.Size = 50;
                p1.Value = to.TieuDe;
                com.Parameters.Add(p1);

                // Use a shorthand syntax to add the id parameter.
                com.Parameters.Add("@tlID", SqlDbType.Int).Value = to.MyTheLoai;
                com.Parameters.Add("@ghichu", SqlDbType.NVarChar, 1000).Value = to.GhiChu;
                com.Parameters.Add("@stt", SqlDbType.Int).Value = to.Stt;
                com.Parameters.Add("@hot", SqlDbType.Int).Value = to.Hot;
                com.Parameters.Add("@HinhAnh", SqlDbType.NVarChar, 50).Value = to.HinhAnh;
                com.Parameters.Add("@ID", SqlDbType.Int).Value = to.Id;
                // Execute the command and process the result.
                if (com.ExecuteNonQuery() == 1)
                {
                    st = true;
                }
                conn.closeCon();
                return st;
            }
        }
        public bool DeleteTour(clsTour to)
        {
            string sql = "Delete from tblTour where ID=@ID";
            bool st = false;
            clsDAConnection conn = new clsDAConnection();
            conn.openCon();
            using (SqlCommand com = conn.getCon().CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = sql;
                com.Parameters.Add("@ID", SqlDbType.Int).Value = to.Id;
                // Execute the command and process the result.
                if (com.ExecuteNonQuery() == 1)
                {
                    st = true;
                }
                conn.closeCon();
                return st;
            }
        }
    }
}
